package com.zql.JxLExcel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zql.Main.GongJu;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/** 
* @author 钟琴隆 E-mail: ai31354907@163.com
* @version 创建时间：2018年11月6日 上午11:11:07 
* 类说明 
*/
public class JxlExcel {
	//private static Connection conn;
	//private static Statement stmt;
	private static ResultSet rs;
public static void main(String[] args) {
	try {
		long start = System.currentTimeMillis();
		String[] title = {"编号","车系ID","车系","保值率","年限","新款报价","省","市","更新时间","状态"};
		String filePath = GongJu.dclj+"/tPremiumRatemodel.xls";
		//String filePath = "f:/tPremiumRatemodel.xls";
		WritableWorkbook wwb;
		OutputStream os = new FileOutputStream(filePath);
		wwb = Workbook.createWorkbook(os);
		WritableSheet sheet = wwb.createSheet("汽车之家二手车保值率", 0);
		//CellFormat cf = wwb.getSheet(0).getCell(1, 0).getCellFormat();
		WritableCellFormat wc = new WritableCellFormat();
		wc.setAlignment(Alignment.CENTRE);
		Label label;
		for(int i=0;i<title.length;i++) {
			label = new Label(i,0,title[i],wc);
			sheet.addCell(label);
		}
		GongJu.getDao();
		String sql = "SELECT \"nDealerID\",\"sDealerName\",\"sBrand\",\"sProvince\",\"sCity\",\"sAddress\",\"sSaleCall\",\"sCustomerServiceCall\",\"sDealerType\",\"sManufacturer\",\"dUpdateTime\",\"sLongitude\",\"sLatitude\" FROM \"201812\".\"tDealer\";";
		rs = GongJu.stmt.executeQuery(sql);
		List<Integer> id00 = new ArrayList<>();
		List<Integer> sid00 = new ArrayList<>();
		List<String> sname00 = new ArrayList<>();
		List<String> spr00 = new ArrayList<>();
		List<Integer> syear00 = new ArrayList<>();
		List<String> sprice00 = new ArrayList<>();
		List<String> suptime00 = new ArrayList<>();
		Excel excel = new Excel();
		while(rs.next()) {
			id00.add(rs.getInt(1));
			sid00.add(rs.getInt(2));
			sname00.add(rs.getString(3));
			spr00.add(rs.getString(4));
			syear00.add(rs.getInt(5));
			sprice00.add(rs.getString(6));
			suptime00.add(rs.getString(7));
		}
		/*excel.setEid(id00);
		excel.setEsid(sid00);
		excel.setEsname(sname00);
		excel.setEpre(spr00);
		excel.setEyear(syear00);
		excel.setEprice(sprice00);
		excel.setEuptime(suptime00);
		jxl.write.Number number;
		for(int j=0;j<excel.getEid().size();j++) {
			number = new jxl.write.Number(0,(j+1),excel.getEid().get(j),wc);
			sheet.addCell(number);
			number = new jxl.write.Number(1,(j+1),excel.getEsid().get(j),wc);
			sheet.addCell(number);
			label = new Label(2,(j+1),excel.getEsname().get(j),wc);
			sheet.addCell(label);
			label = new Label(3,(j+1),excel.getEpre().get(j),wc);
			sheet.addCell(label);
			number = new jxl.write.Number(4,(j+1),excel.getEyear().get(j),wc);
			sheet.addCell(number);
			label = new Label(5,(j+1),excel.getEprice().get(j),wc);
			sheet.addCell(label);
			label = new Label(8,(j+1),excel.getEuptime().get(j),wc);
			sheet.addCell(label);
		}*/
		// 写入数据
		wwb.write();
		// 关闭文件
		wwb.close();
		long end = System.currentTimeMillis();
		System.out.println("----完成该操作共用的时间是:"+(end-start)/1000);
	}catch (SQLException e) {
		e.printStackTrace();
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (RowsExceededException e) {
		e.printStackTrace();
	} catch (WriteException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}finally {
			try {
				if(rs != null) {
					rs.close();
				}
				if(GongJu.stmt != null) {
					GongJu.stmt.close();
				}
				if(GongJu.conn != null) {
					GongJu.conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
}
}
 